package util;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;

public class DBHelper {
    //12
    public Connection getConcection(){
        Connection connection = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://127.0.0.1:3306/books?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
            String username = "root";
            String password = "4321";
            System.out.println(connection);
            System.out.println(1);
            connection = DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            System.out.println(2);
            e.printStackTrace();
        } catch (SQLException e) {
            System.out.println(3);
            e.printStackTrace();
        }finally {
            System.out.println(4);
            System.out.println(connection);
        }
        return connection;
    }
    //7
    public void closeAll(ResultSet resultSet, PreparedStatement preparedStatement, Connection connection) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (preparedStatement != null) {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //45(1-7汇总)传入3，返回6
    public int selectInfoCount(String sql, Object... objects) {
        System.out.println(objects[0]);
        System.out.println(objects[1]);
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int count = 0;
        try {
            connection = getConcection();
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < objects.length; i++) {
                preparedStatement.setObject(i + 1, objects[i]);
            }
            resultSet = preparedStatement.executeQuery();

            System.out.println(preparedStatement);
            if (resultSet.next())
                count = resultSet.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll(resultSet, preparedStatement, connection);
        }
        System.out.println(count);
        return count;
    }


    //45(1-7汇总)传入3，返回6
    public ArrayList selectInfo(String sql, Class classOne, Object... objects) {
        ArrayList list = new ArrayList();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = getConcection();
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < objects.length; i++) {
                preparedStatement.setObject(i + 1, objects[i]);
            }
            System.out.println(sql);
            resultSet = preparedStatement.executeQuery();
            System.out.println(preparedStatement);
            while (resultSet.next()) {

                //根据classOne获得该字节码的对象
                Object o = classOne.newInstance();
                //给所有对象属性赋值
                Field[] declaredFields = classOne.getDeclaredFields();
                for (Field f :
                        declaredFields) {
                    f.setAccessible(true);
                    f.set(o, resultSet.getObject(f.getName()));
                }

                list.add(o);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } finally {
            closeAll(resultSet, preparedStatement, connection);
        }
        return list;
    }

    /*//求符合条件的数据总个数
    public int selectInfoCount(String sql, Object... objects){
        Connection connection = null;
        int count = 0;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = getConcection();
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < objects.length; i++) {
                preparedStatement.setObject(i + 1, objects[i]);
            }
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next())//naze?
                count = resultSet.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll(resultSet, preparedStatement, connection);
        }
        return count;
    }
*/
    public int changeInfo(String sql, Object... objects) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        int count = 0;
        try {
            connection = getConcection();
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < objects.length; i++) {
                preparedStatement.setObject(i + 1, objects[i]);
            }
            count = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll(null, preparedStatement, connection);
        }
        return count;
    }
}
